'use strict';
const Service = require('egg').Service;
const path = require('path');
const fs = require('fs');

class ToolsService extends Service {

  get path() {
    return path.join(this.app.config.baseDir, 'app', 'public', 'upload', 'bak');
  }

  // 获取数据库表状态
  async getTableStatus() {
    const sql = 'SHOW TABLE STATUS';
    let results = await this.ctx.model.query(sql, { raw: true });
    if (Array.isArray(results) && results.length < 5) results = results[0];
    if (Array.isArray(results) && results.length < 5) results = results[0];
    const data = [];
    let total = 0;
    for (const item of results) {
      const size = item.Data_length + item.Index_length;
      total += size;
      item.size = this.ctx.helper.bytes_to_size(size);
      data.push(item);
    }
    return { data, total };
  }

  // 获取数据库表备份
  async getBak(tables = []) {
    let out_str = '';
    let table_sql = '';
    const file_path = path.join(this.path, 'sql_') + this.ctx.helper.time_to_date() + '_' + this.ctx.helper.date_to_time();
    if (tables.length) {
      const start_sql = 'SET SQL_QUOTE_SHOW_CREATE = 1';
      await this.ctx.model.query(start_sql, { raw: true });
      for (const table of tables) {
        out_str += '-- 表的结构 ' + table + ' \n';
        out_str += 'DROP TABLE IF EXISTS ' + table + ';\n';
        const sql = 'SHOW CREATE TABLE ' + table;
        let tmp = await this.ctx.model.query(sql, { raw: true });
        if (Array.isArray(tmp)) tmp = tmp[0];
        if (Array.isArray(tmp)) tmp = tmp[0];
        out_str += tmp['Create Table'] + ' ;\n\n';
      }
      table_sql = out_str;
      out_str = '';
      const pre = '-- -----------------------------------------------------------\n';
      const file_n = 1;
      const temp_tables = [];
      for (const table of tables) {
        temp_tables.push(table);
        out_str += '\n\n-- 转存表中的数据：' + table + ' \n';
        const sql = 'SHOW TABLE STATUS LIKE "' + table + '";';
        let tmp = await this.ctx.model.query(sql, { raw: true });
        if (Array.isArray(tmp)) tmp = tmp[0];
        if (Array.isArray(tmp)) tmp = tmp[0];
        const page = Math.ceil(tmp.Rows / 10000) - 1;
        for (let i = 0; i <= page; i++) {
          const select_sql = 'SELECT * FROM ' + table + ' LIMIT ' + (i * 10000) + ', 10000';
          const query_res = await this.ctx.model.query(select_sql, { raw: true, type: 'SELECT' });
          for (const val of query_res) {
            let tem_sql = '';
            const insert_val = Object.values(val);
            tem_sql = 'INSERT INTO `' + table + "` VALUES ('" + insert_val.join("','") + "');\n";
            out_str += tem_sql;
          }
        }
      }
      if (table_sql.length + out_str.length > 0) {
        let sql_no = '\n-- Time: ' + this.ctx.helper.time_to_date(null, 6) + '\n' +
            '-- -----------------------------------------------------------\n' +
            '-- SQLFile Label：--' + file_n + '\n-- -----------------------------------------------------------\n\n\n';
        sql_no = '-- Description:备份的数据表[结构] ' + tables.join(',') + '\n-- Description:备份的数据表[数据] ' + temp_tables.join(',') + sql_no;
        const file = file_path + '_' + file_n + '.sql';
        out_str = file_n === 1 ? pre + sql_no + table_sql + out_str : pre + sql_no + out_str;
        if (!this.ctx.helper.create_file(file, out_str, { flag: 'a' })) {
          return;
        }
      }
    }
    return out_str;
  }

  // sql文件列表
  getSqlList(file_path = '') {
    file_path = file_path ? file_path : this.path;
    const file_list = [];
    let size_total = 0;
    if (fs.existsSync(file_path)) {
      const res = fs.readdirSync(file_path);
      if (res.length) {
        const sql_list = res.filter(val => {
          return /^sql_.+/.test(val);
        });
        sql_list.forEach(item => {
          const file_obj = fs.statSync(path.join(file_path, item));
          size_total += file_obj.size;
          const createTime = this.ctx.helper.time_to_date(this.ctx.helper.date_to_time(file_obj.ctime), 6);
          const file_info = { name: item, size: this.ctx.helper.bytes_to_size(file_obj.size), createTime };
          file_list.push(file_info);
        });
      }
    }
    return { lists: file_list, size_total: this.ctx.helper.bytes_to_size(size_total) };
  }

  // sql删除
  removeSql(file_name, file_path = '') {
    if (!file_name) return false;
    file_path = file_path ? file_path : this.path;
    const link = path.join(file_path, file_name);
    if (fs.existsSync(link)) {
      fs.unlinkSync(link);
      return true;
    }
    return false;
  }

  // sql下载
  downloadSql(file_name, file_path = '') {
    if (!file_name) return false;
    file_path = file_path ? file_path : this.path;
    const link = path.resolve(file_path, file_name);
    if (!fs.existsSync(link)) return false;
    const fsStat = fs.statSync(link);
    if (fsStat.isDirectory()) return false;
    const reader = fs.createReadStream(link);
    return reader;
  }

  // 表的优化和修复
  async tableOp(type, table) {
    if (!table || !table) return false;
    let result = false;
    switch (type) {
      case 'optimize': // 优化
      case 1:
      {
        const sql = 'OPTIMIZE TABLE `' + table + '`';
        const res = await this.ctx.model.query(sql, { raw: true });
        result = !!res;
        break;
      }
      case 'repair': // 修复
      case 2:
      {
        const sql = 'REPAIR TABLE `' + table + '`';
        const res = await this.ctx.model.query(sql, { raw: true });
        result = !!res;
        break;
      }
      default: break;
    }
    return result;
  }

}

module.exports = ToolsService;
